#!/bin/bash

APP=edu
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

dwd_trade_cart_add_inc="
insert overwrite table ${APP}.dwd_trade_cart_add_inc partition (dt='$do_date')
select
	data.id,
	data.user_id,
	data.course_id,
	data.cart_price,
	data.session_id,
	date_format(data.create_time, 'yyyy-MM-dd'),
	data.create_time
from ods_cart_info_inc
where dt='$do_date' and type='insert';
"

dwd_trade_order_detail_inc="
insert overwrite table dwd_trade_order_detail_inc partition (dt='$do_date')
select
    od.id,
	od.course_id,
	od.order_id,
	od.user_id,
	oi.session_id,
	oi.province_id,
	date_format(od.create_time, 'yyyy-MM-dd'),
	od.create_time,
	od.origin_amount
from
(
	select
		data.id,
		data.course_id,
		data.order_id,
		data.user_id,
		data.origin_amount,
		data.create_time
	from ods_order_detail_inc
	where dt = '$do_date' and type = 'insert'
) od
left join
(
	select
		data.id,
		data.session_id,
		data.province_id
	from ods_order_info_inc
	where dt = '$do_date' and type = 'insert'
) oi
on od.order_id = oi.id;
"

dwd_trade_pay_detail_suc_inc="
insert overwrite table dwd_trade_pay_detail_suc_inc partition(dt='$do_date');
select
	pi.id,
	pi.order_id,
	oi.user_id,
	od.course_id,
	pi.payment_type,
	date_format(pi.callback_time, 'yyyy-MM-dd'),
	pi.callback_time,
	od.origin_amount
from
(
	select
		data.course_id,
		data.order_id,
		data.origin_amount,
	from ods_order_detail_inc
	where dt = '$do_date' and type = 'insert'
) od
join
(
	select
		data.id,
		data.order_id,
		data.payment_type,
		data.callback_time,
	from ods_payment_info_inc
	where dt = '$do_date' and type = 'update' 
	and array_contains(map_keys(old), 'payment_status')
    and data.payment_status='1602'	
) pi
on od.order_id=pi.order_id
left join
(
	select
		data.id,
		data.user_id,
		data.province_id
	from ods_order_info_inc
	where dt = '$do_date' and type = 'insert';
) oi
on od.order_id=oi.id;
"

dwd_traffic_page_view_inc="
insert overwrite table dwd_traffic_page_view_inc partition (dt='$do_date')
select
    province_name,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    item_type,
    date_format(from_utc_timestamp(cast(ts as bigint),'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(cast(ts as bigint),'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,
    concat(mid_id,'-',last_value(session_start_point,true) over (partition by mid_id order by ts)) session_id,
    during_time
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        page.item_type,
        ts,
        if(page.last_page_id is null,ts,null) session_start_point
    from ods_log_inc
    where dt='$do_date'
    and page is not null
)log
left join
(
    select
        id province_id,
        name province_name
    from ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.province_id;
"

dwd_play_video_play_inc="
insert overwrite table dwd_play_video_play_inc partition(dt='$do_date')  
select
   user_id,
   t2.course_id   course_id,
   t2.chapter_id  chapter_id,
   video_id,
   play_sec,
   position_sec,
   date_format(from_utc_timestamp(t1.ts,'GMT+8'),'yyyy-MM-dd') date_id
from
(
   select
      common.uid   user_id,
      appVideo.video_id  video_id,
      appVideo.play_sec  play_sec,
      appVideo.position_sec  position_sec,
      ts
   from ods_log_inc
   where dt='$do_date'
   and common.uid is not null
   and appVideo.video_id is not null
) t1
join
(
   select
      id,
      chapter_id,
      course_id
   from ods_video_info_full
) t2
on video_id=t2.id;
"

dwd_exam_paper_exam_inc="
insert overwrite table dwd_exam_paper_exam_inc partition(dt='$do_date')
select
	data.id,
	data.user_id,
	data.paper_id,
	date_format(create_time, 'yyyy-MM-dd') date_id, 
	data.create_time,
	data.submit_time,
	data.score,
	data.duration_sec
from ods_test_exam_inc
where dt = '$do_date' and type = 'insert';
"

case $1 in
    "dwd_trade_cart_add_inc" )
	    hive -e "$dwd_trade_cart_add_inc"
	;;


	"dwd_trade_order_detail_inc" )
	    hive -e "$dwd_trade_order_detail_inc"
	;;
	"dwd_trade_pay_detail_suc_inc" )
	    hive -e "$dwd_trade_pay_detail_suc_inc"
	;;
	
	"dwd_exam_paper_exam_inc" )
	    hive -e "$dwd_exam_paper_exam_inc"
	;;
	
	
	"dwd_play_video_play_inc" )
		hive -e "$dwd_play_video_play_inc"
	;;
	
	"dwd_traffic_page_view_inc" )
        hive -e "$dwd_traffic_page_view_inc"
    ;;
    
	"all" )
	    hive -e "$dwd_trade_cart_add_inc
		$dwd_trade_order_detail_inc$dwd_trade_pay_detail_suc_inc
		$dwd_exam_paper_exam_inc$dwd_play_video_play_inc
		$dwd_traffic_page_view_inc"
	;;
